/**
 * 
 */

import java.util.Arrays;
import org.jooq.Queries;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import org.junit.Test;

/**
 * @author Kane
 *
 */
public class JOOQTest {
  
  private void translate(String sql) {
    Queries queries = DSL.using(SQLDialect.MYSQL_5_7).parser().parse(sql);
    System.out.println(DSL.using(SQLDialect.ORACLE11G).render(queries));
  }
  
  @Test
  public void testCreateIndex() throws Exception {
    String sql = "create index idx2 on T_Person(FName,FAge);";
    translate(sql);
  }
  
  @Test
  public void testCreateTable() throws Exception {
    String sql = "create Table T_StockFlow(FBillId varchar(20),FDetailId varchar(20),FDate date,FBillTypeName  varchar(20),FAmount decimal(10,2),primary key (FBillId,FDetailId));";
    translate(sql);
  }
  
  @Test
  public void testInsertInto() throws Exception {
    String sql = "insert into T_SaleInvoice values('sv001','sv001','p001',{'2007-08-8'});";
    translate(sql);
  }
  
  @Test
  public void testInsertSelect() throws Exception {
    String sql = "insert into T_StockFlow(FBillId,FDetailId,FDate,FBillTypeName,FAmount) "
        + "select detail.FParentId,detail.FId,parent.FDate,'销售发票',detail.FCount*detail.FPrice from T_SaleInvoiceDetails detail left join T_SaleInvoice parent on parent.FId=detail.FParentId;";
    translate(sql);
  }
  
  @Test
  public void testUpdate() throws Exception {
    String sql = "update T_SaleInvoice set FDate=DateAdd(dd,-1,Now());";
    translate(sql);
  }
  
  @Test
  public void testSelectTop() throws Exception {
    String sql = "select top 10 * from T_SaleInvoice;";
    translate(sql);
  }
  
  @Test
  public void testSelectJoin() throws Exception {
    String sql = "select trim(person.FName),bill.FNumber from T_SaleInvoice as bill left join T_Person person on bill.FSalesPersonId=person.FId;";
    translate(sql);
  }
  
  @Test
  public void testSelectSub() throws Exception {
    String sql = "select * from T_StockFlow where FBillId in('a','b') or FBillId in(select FBillId from T_StockFlow)";
    translate(sql);
  }
  
  @Test
  public void testDeleteSub() throws Exception {
    String sql = "delete from T_SaleInvoice where FId in(select top 5 FParentId from T_SaleInvoiceDetails)";
    translate(sql);
  }
  
  @Test
  public void testSelectFunc() throws Exception {
    String sql = "select CONVERT('VARCHAR',3),CONVERT('INT','3'),CURTIME(),DATEDIFF(NOW(),CURDATE()),DATEDIFF(minute,NOW(),CURDATE());";
    translate(sql);
  }
  
  @Test
  public void testSelectFunc2() throws Exception {
    String sql = "select TO_DATE('2008-08-08'),MONTHS_BETWEEN(NOW(),{'2008-08-08'}),DAYS_BETWEEN(NOW(),{'2008-08-08'});";
    translate(sql);
  }
  
  @Test
  public void testSelectFunc3() throws Exception {
    String sql = "select LEFTSTR('verycd',2),RIGHTSTR('cownew',3),LENGTH('cde'),LCASE('Cool!'),LTRIM('  aa  d ');";
    translate(sql);
  }
  
  @Test
  public void testSelectFunc4() throws Exception {
    String sql = "select REPLACE('you and me and he','and','or'),RTRIM(' aa d '),SUBSTRING('whitehouse',1,4),TRIM(' abe da '),UCASE('abc'),TOCHAR(1),TO_CHAR(now());";
    translate(sql);
  }
  
}
